import pulp
import numpy as np
import pandas as pd
def create_crops_array(file_name):
    # 读取Excel文件
    df = pd.read_excel(file_name)

    # 定义数组的维度
    num_crops = 41  # 假设作物编号的取值范围是1到54
    num_fields = 54  # 假设地块编号的取值范围是1到41
    num_periods = 2  # 假设种植季节的取值范围是1到2

    # 创建一个三维数组，初始化为零
    p = np.zeros((num_crops, num_fields, num_periods))

    # 遍历DataFrame的每一行
    for index, row in df.iterrows():
        crop_id = int(row['作物编号'])-1   # 作物编号
        field_id = int(row['地块编号'])   # 地块编号
        period_id = int(row['种植季节'])   # 种植季节
        area = row['种植面积/亩']  # 种植面积

        # 将种植面积存入对应的数组位置
        p[crop_id, field_id, period_id] = area

    return p

# 输入Excel文件路径
file_name = "D:\planting\初始解.xlsx"
crops_array = create_crops_array(file_name)

# 打印数组以验证
print(crops_array)
# 创建一个线性规划问题实例
prob = pulp.LpProblem("Crop_Optimization", pulp.LpMaximize)

# 决策变量
x = pulp.LpVariable.dicts("x", (range(41), range(54), range(16)), lowBound=0, cat='Continuous')
y = pulp.LpVariable.dicts("y", (range(41), range(54), range(16)), cat='Binary')
c = pulp.LpVariable.dicts("c", (range(41), range(54), range(16)), lowBound=0, cat='Continuous')
# 农作物名称列表
crops = [
    "黄豆", "黑豆", "红豆", "绿豆", "爬豆", "小麦", "玉米", "谷子", "高粱", "黍子",
    "荞麦", "南瓜", "红薯", "莜麦", "大麦", "水稻", "豇豆", "刀豆", "芸豆", "土豆",
    "西红柿", "茄子", "菠菜", "青椒", "菜花", "包菜", "油麦菜", "小青菜", "黄瓜",
    "生菜", "辣椒", "空心菜", "黄心菜", "芹菜", "大白菜", "白萝卜", "红萝卜", "榆黄菇",
    "香菇", "白灵菇", "羊肚菌"
]
# 地块信息
fields = {
    'A1': '平旱地', 'A2': '平旱地', 'A3': '平旱地', 'A4': '平旱地', 'A5': '平旱地', 'A6': '平旱地',
    'B1': '梯田', 'B2': '梯田', 'B3': '梯田', 'B4': '梯田', 'B5': '梯田', 'B6': '梯田',
    'B7': '梯田', 'B8': '梯田', 'B9': '梯田', 'B10': '梯田', 'B11': '梯田', 'B12': '梯田',
    'B13': '梯田', 'B14': '梯田',
    'C1': '山坡地', 'C2': '山坡地', 'C3': '山坡地', 'C4': '山坡地', 'C5': '山坡地', 'C6': '山坡地',
    'D1': '水浇地', 'D2': '水浇地', 'D3': '水浇地', 'D4': '水浇地', 'D5': '水浇地', 'D6': '水浇地',
    'D7': '水浇地', 'D8': '水浇地',
    'E1': '普通大棚', 'E2': '普通大棚', 'E3': '普通大棚', 'E4': '普通大棚', 'E5': '普通大棚',
    'E6': '普通大棚', 'E7': '普通大棚', 'E8': '普通大棚', 'E9': '普通大棚', 'E10': '普通大棚',
    'E11': '普通大棚', 'E12': '普通大棚', 'E13': '普通大棚', 'E14': '普通大棚', 'E15': '普通大棚',
    'E16': '普通大棚',
    'F1': '智慧大棚', 'F2': '智慧大棚', 'F3': '智慧大棚', 'F4': '智慧大棚'
}
# 地块数量
num_fields = len(fields)
num_crops = len(crops)
num_periods = 16  # 8年，每年2季
d_data = [("黄豆","平旱地","第一季",72.0), ("黄豆","平旱地","第二季",0.0), ("黑豆","平旱地","第一季",0.0), ("黑豆","平旱地","第二季",0.0), ("红豆","平旱地","第一季",0.0), ("红豆","平旱地","第二季",0.0), ("绿豆","平旱地","第一季",68.0), ("绿豆","平旱地","第二季",0.0), ("爬豆","平旱地","第一季",0.0), ("爬豆","平旱地","第二季",0.0), ("小麦","平旱地","第一季",80.0), ("小麦","平旱地","第二季",0.0), ("玉米","平旱地","第一季",90.0), ("玉米","平旱地","第二季",0.0), ("谷子","平旱地","第一季",55.0), ("谷子","平旱地","第二季",0.0), ("高粱","平旱地","第一季",0.0), ("高粱","平旱地","第二季",0.0), ("黍子","平旱地","第一季",0.0), ("黍子","平旱地","第二季",0.0), ("荞麦","平旱地","第一季",0.0), ("荞麦","平旱地","第二季",0.0), ("南瓜","平旱地","第一季",0.0), ("南瓜","平旱地","第二季",0.0), ("红薯","平旱地","第一季",0.0), ("红薯","平旱地","第二季",0.0), ("莜麦","平旱地","第一季",0.0), ("莜麦","平旱地","第二季",0.0), ("大麦","平旱地","第一季",0.0), ("大麦","平旱地","第二季",0.0), ("水稻","平旱地","第一季",0.0), ("水稻","平旱地","第二季",0.0), ("豇豆","平旱地","第一季",0.0), ("豇豆","平旱地","第二季",0.0), ("刀豆","平旱地","第一季",0.0), ("刀豆","平旱地","第二季",0.0), ("芸豆","平旱地","第一季",0.0), ("芸豆","平旱地","第二季",0.0), ("土豆","平旱地","第一季",0.0), ("土豆","平旱地","第二季",0.0), ("西红柿","平旱地","第一季",0.0), ("西红柿","平旱地","第二季",0.0), ("茄子","平旱地","第一季",0.0), ("茄子","平旱地","第二季",0.0), ("菠菜","平旱地","第一季",0.0), ("菠菜","平旱地","第二季",0.0), ("青椒","平旱地","第一季",0.0), ("青椒","平旱地","第二季",0.0), ("菜花","平旱地","第一季",0.0), ("菜花","平旱地","第二季",0.0), ("包菜","平旱地","第一季",0.0), ("包菜","平旱地","第二季",0.0), ("油麦菜","平旱地","第一季",0.0), ("油麦菜","平旱地","第二季",0.0), ("小青菜","平旱地","第一季",0.0), ("小青菜","平旱地","第二季",0.0), ("黄瓜","平旱地","第一季",0.0), ("黄瓜","平旱地","第二季",0.0), ("生菜","平旱地","第一季",0.0), ("生菜","平旱地","第二季",0.0), ("辣椒","平旱地","第一季",0.0), ("辣椒","平旱地","第二季",0.0), ("空心菜","平旱地","第一季",0.0), ("空心菜","平旱地","第二季",0.0), ("黄心菜","平旱地","第一季",0.0), ("黄心菜","平旱地","第二季",0.0), ("芹菜","平旱地","第一季",0.0), ("芹菜","平旱地","第二季",0.0), ("大白菜","平旱地","第一季",0.0), ("大白菜","平旱地","第二季",0.0), ("白萝卜","平旱地","第一季",0.0), ("白萝卜","平旱地","第二季",0.0), ("红萝卜","平旱地","第一季",0.0), ("红萝卜","平旱地","第二季",0.0), ("榆黄菇","平旱地","第一季",0.0), ("榆黄菇","平旱地","第二季",0.0), ("香菇","平旱地","第一季",0.0), ("香菇","平旱地","第二季",0.0), ("白灵菇","平旱地","第一季",0.0), ("白灵菇","平旱地","第二季",0.0), ("羊肚菌","平旱地","第一季",0.0), ("羊肚菌","平旱地","第二季",0.0), ("黄豆","梯田","第一季",60.0), ("黄豆","梯田","第二季",0.0), ("黑豆","梯田","第一季",46.0), ("黑豆","梯田","第二季",0.0), ("红豆","梯田","第一季",40.0), ("红豆","梯田","第二季",0.0), ("绿豆","梯田","第一季",28.0), ("绿豆","梯田","第二季",0.0), ("爬豆","梯田","第一季",25.0), ("爬豆","梯田","第二季",0.0), ("小麦","梯田","第一季",115.0), ("小麦","梯田","第二季",0.0), ("玉米","梯田","第一季",45.0), ("玉米","梯田","第二季",0.0), ("谷子","梯田","第一季",130.0), ("谷子","梯田","第二季",0.0), ("高粱","梯田","第一季",50.0), ("高粱","梯田","第二季",0.0), ("黍子","梯田","第一季",25.0), ("黍子","梯田","第二季",0.0), ("荞麦","梯田","第一季",0.0), ("荞麦","梯田","第二季",0.0), ("南瓜","梯田","第一季",0.0), ("南瓜","梯田","第二季",0.0), ("红薯","梯田","第一季",0.0), ("红薯","梯田","第二季",0.0), ("莜麦","梯田","第一季",35.0), ("莜麦","梯田","第二季",0.0), ("大麦","梯田","第一季",20.0), ("大麦","梯田","第二季",0.0), ("水稻","梯田","第一季",0.0), ("水稻","梯田","第二季",0.0), ("豇豆","梯田","第一季",0.0), ("豇豆","梯田","第二季",0.0), ("刀豆","梯田","第一季",0.0), ("刀豆","梯田","第二季",0.0), ("芸豆","梯田","第一季",0.0), ("芸豆","梯田","第二季",0.0), ("土豆","梯田","第一季",0.0), ("土豆","梯田","第二季",0.0), ("西红柿","梯田","第一季",0.0), ("西红柿","梯田","第二季",0.0), ("茄子","梯田","第一季",0.0), ("茄子","梯田","第二季",0.0), ("菠菜","梯田","第一季",0.0), ("菠菜","梯田","第二季",0.0), ("青椒","梯田","第一季",0.0), ("青椒","梯田","第二季",0.0), ("菜花","梯田","第一季",0.0), ("菜花","梯田","第二季",0.0), ("包菜","梯田","第一季",0.0), ("包菜","梯田","第二季",0.0), ("油麦菜","梯田","第一季",0.0), ("油麦菜","梯田","第二季",0.0), ("小青菜","梯田","第一季",0.0), ("小青菜","梯田","第二季",0.0), ("黄瓜","梯田","第一季",0.0), ("黄瓜","梯田","第二季",0.0), ("生菜","梯田","第一季",0.0), ("生菜","梯田","第二季",0.0), ("辣椒","梯田","第一季",0.0), ("辣椒","梯田","第二季",0.0), ("空心菜","梯田","第一季",0.0), ("空心菜","梯田","第二季",0.0), ("黄心菜","梯田","第一季",0.0), ("黄心菜","梯田","第二季",0.0), ("芹菜","梯田","第一季",0.0), ("芹菜","梯田","第二季",0.0), ("大白菜","梯田","第一季",0.0), ("大白菜","梯田","第二季",0.0), ("白萝卜","梯田","第一季",0.0), ("白萝卜","梯田","第二季",0.0), ("红萝卜","梯田","第一季",0.0), ("红萝卜","梯田","第二季",0.0), ("榆黄菇","梯田","第一季",0.0), ("榆黄菇","梯田","第二季",0.0), ("香菇","梯田","第一季",0.0), ("香菇","梯田","第二季",0.0), ("白灵菇","梯田","第一季",0.0), ("白灵菇","梯田","第二季",0.0), ("羊肚菌","梯田","第一季",0.0), ("羊肚菌","梯田","第二季",0.0), ("黄豆","山坡地","第一季",15.0), ("黄豆","山坡地","第二季",0.0), ("黑豆","山坡地","第一季",0.0), ("黑豆","山坡地","第二季",0.0), ("红豆","山坡地","第一季",20.0), ("红豆","山坡地","第二季",0.0), ("绿豆","山坡地","第一季",0.0), ("绿豆","山坡地","第二季",0.0), ("爬豆","山坡地","第一季",0.0), ("爬豆","山坡地","第二季",0.0), ("小麦","山坡地","第一季",27.0), ("小麦","山坡地","第二季",0.0), ("玉米","山坡地","第一季",0.0), ("玉米","山坡地","第二季",0.0), ("谷子","山坡地","第一季",0.0), ("谷子","山坡地","第二季",0.0), ("高粱","山坡地","第一季",0.0), ("高粱","山坡地","第二季",0.0), ("黍子","山坡地","第一季",0.0), ("黍子","山坡地","第二季",0.0), ("荞麦","山坡地","第一季",15.0), ("荞麦","山坡地","第二季",0.0), ("南瓜","山坡地","第一季",13.0), ("南瓜","山坡地","第二季",0.0), ("红薯","山坡地","第一季",18.0), ("红薯","山坡地","第二季",0.0), ("莜麦","山坡地","第一季",0.0), ("莜麦","山坡地","第二季",0.0), ("大麦","山坡地","第一季",0.0), ("大麦","山坡地","第二季",0.0), ("水稻","山坡地","第一季",0.0), ("水稻","山坡地","第二季",0.0), ("豇豆","山坡地","第一季",0.0), ("豇豆","山坡地","第二季",0.0), ("刀豆","山坡地","第一季",0.0), ("刀豆","山坡地","第二季",0.0), ("芸豆","山坡地","第一季",0.0), ("芸豆","山坡地","第二季",0.0), ("土豆","山坡地","第一季",0.0), ("土豆","山坡地","第二季",0.0), ("西红柿","山坡地","第一季",0.0), ("西红柿","山坡地","第二季",0.0), ("茄子","山坡地","第一季",0.0), ("茄子","山坡地","第二季",0.0), ("菠菜","山坡地","第一季",0.0), ("菠菜","山坡地","第二季",0.0), ("青椒","山坡地","第一季",0.0), ("青椒","山坡地","第二季",0.0), ("菜花","山坡地","第一季",0.0), ("菜花","山坡地","第二季",0.0), ("包菜","山坡地","第一季",0.0), ("包菜","山坡地","第二季",0.0), ("油麦菜","山坡地","第一季",0.0), ("油麦菜","山坡地","第二季",0.0), ("小青菜","山坡地","第一季",0.0), ("小青菜","山坡地","第二季",0.0), ("黄瓜","山坡地","第一季",0.0), ("黄瓜","山坡地","第二季",0.0), ("生菜","山坡地","第一季",0.0), ("生菜","山坡地","第二季",0.0), ("辣椒","山坡地","第一季",0.0), ("辣椒","山坡地","第二季",0.0), ("空心菜","山坡地","第一季",0.0), ("空心菜","山坡地","第二季",0.0), ("黄心菜","山坡地","第一季",0.0), ("黄心菜","山坡地","第二季",0.0), ("芹菜","山坡地","第一季",0.0), ("芹菜","山坡地","第二季",0.0), ("大白菜","山坡地","第一季",0.0), ("大白菜","山坡地","第二季",0.0), ("白萝卜","山坡地","第一季",0.0), ("白萝卜","山坡地","第二季",0.0), ("红萝卜","山坡地","第一季",0.0), ("红萝卜","山坡地","第二季",0.0), ("榆黄菇","山坡地","第一季",0.0), ("榆黄菇","山坡地","第二季",0.0), ("香菇","山坡地","第一季",0.0), ("香菇","山坡地","第二季",0.0), ("白灵菇","山坡地","第一季",0.0), ("白灵菇","山坡地","第二季",0.0), ("羊肚菌","山坡地","第一季",0.0), ("羊肚菌","山坡地","第二季",0.0), ("黄豆","水浇地","第一季",0.0), ("黄豆","水浇地","第二季",0.0), ("黑豆","水浇地","第一季",0.0), ("黑豆","水浇地","第二季",0.0), ("红豆","水浇地","第一季",0.0), ("红豆","水浇地","第二季",0.0), ("绿豆","水浇地","第一季",0.0), ("绿豆","水浇地","第二季",0.0), ("爬豆","水浇地","第一季",0.0), ("爬豆","水浇地","第二季",0.0), ("小麦","水浇地","第一季",0.0), ("小麦","水浇地","第二季",0.0), ("玉米","水浇地","第一季",0.0), ("玉米","水浇地","第二季",0.0), ("谷子","水浇地","第一季",0.0), ("谷子","水浇地","第二季",0.0), ("高粱","水浇地","第一季",0.0), ("高粱","水浇地","第二季",0.0), ("黍子","水浇地","第一季",0.0), ("黍子","水浇地","第二季",0.0), ("荞麦","水浇地","第一季",0.0), ("荞麦","水浇地","第二季",0.0), ("南瓜","水浇地","第一季",0.0), ("南瓜","水浇地","第二季",0.0), ("红薯","水浇地","第一季",0.0), ("红薯","水浇地","第二季",0.0), ("莜麦","水浇地","第一季",0.0), ("莜麦","水浇地","第二季",0.0), ("大麦","水浇地","第一季",0.0), ("大麦","水浇地","第二季",0.0), ("水稻","水浇地","第一季",42.0), ("水稻","水浇地","第二季",0.0), ("豇豆","水浇地","第一季",10.0), ("豇豆","水浇地","第二季",0.0), ("刀豆","水浇地","第一季",12.0), ("刀豆","水浇地","第二季",0.0), ("芸豆","水浇地","第一季",0.0), ("芸豆","水浇地","第二季",0.0), ("土豆","水浇地","第一季",15.0), ("土豆","水浇地","第二季",0.0), ("西红柿","水浇地","第一季",14.0), ("西红柿","水浇地","第二季",0.0), ("茄子","水浇地","第一季",6.0), ("茄子","水浇地","第二季",0.0), ("菠菜","水浇地","第一季",0.0), ("菠菜","水浇地","第二季",0.0), ("青椒","水浇地","第一季",0.0), ("青椒","水浇地","第二季",0.0), ("菜花","水浇地","第一季",0.0), ("菜花","水浇地","第二季",0.0), ("包菜","水浇地","第一季",0.0), ("包菜","水浇地","第二季",0.0), ("油麦菜","水浇地","第一季",0.0), ("油麦菜","水浇地","第二季",0.0), ("小青菜","水浇地","第一季",10.0), ("小青菜","水浇地","第二季",0.0), ("黄瓜","水浇地","第一季",0.0), ("黄瓜","水浇地","第二季",0.0), ("生菜","水浇地","第一季",0.0), ("生菜","水浇地","第二季",0.0), ("辣椒","水浇地","第一季",0.0), ("辣椒","水浇地","第二季",0.0), ("空心菜","水浇地","第一季",0.0), ("空心菜","水浇地","第二季",0.0), ("黄心菜","水浇地","第一季",0.0), ("黄心菜","水浇地","第二季",0.0), ("芹菜","水浇地","第一季",0.0), ("芹菜","水浇地","第二季",0.0), ("大白菜","水浇地","第一季",0.0), ("大白菜","水浇地","第二季",30.0), ("白萝卜","水浇地","第一季",0.0), ("白萝卜","水浇地","第二季",25.0), ("红萝卜","水浇地","第一季",0.0), ("红萝卜","水浇地","第二季",12.0), ("榆黄菇","水浇地","第一季",0.0), ("榆黄菇","水浇地","第二季",0.0), ("香菇","水浇地","第一季",0.0), ("香菇","水浇地","第二季",0.0), ("白灵菇","水浇地","第一季",0.0), ("白灵菇","水浇地","第二季",0.0), ("羊肚菌","水浇地","第一季",0.0), ("羊肚菌","水浇地","第二季",0.0), ("黄豆","普通大棚","第一季",0.0), ("黄豆","普通大棚","第二季",0.0), ("黑豆","普通大棚","第一季",0.0), ("黑豆","普通大棚","第二季",0.0), ("红豆","普通大棚","第一季",0.0), ("红豆","普通大棚","第二季",0.0), ("绿豆","普通大棚","第一季",0.0), ("绿豆","普通大棚","第二季",0.0), ("爬豆","普通大棚","第一季",0.0), ("爬豆","普通大棚","第二季",0.0), ("小麦","普通大棚","第一季",0.0), ("小麦","普通大棚","第二季",0.0), ("玉米","普通大棚","第一季",0.0), ("玉米","普通大棚","第二季",0.0), ("谷子","普通大棚","第一季",0.0), ("谷子","普通大棚","第二季",0.0), ("高粱","普通大棚","第一季",0.0), ("高粱","普通大棚","第二季",0.0), ("黍子","普通大棚","第一季",0.0), ("黍子","普通大棚","第二季",0.0), ("荞麦","普通大棚","第一季",0.0), ("荞麦","普通大棚","第二季",0.0), ("南瓜","普通大棚","第一季",0.0), ("南瓜","普通大棚","第二季",0.0), ("红薯","普通大棚","第一季",0.0), ("红薯","普通大棚","第二季",0.0), ("莜麦","普通大棚","第一季",0.0), ("莜麦","普通大棚","第二季",0.0), ("大麦","普通大棚","第一季",0.0), ("大麦","普通大棚","第二季",0.0), ("水稻","普通大棚","第一季",0.0), ("水稻","普通大棚","第二季",0.0), ("豇豆","普通大棚","第一季",1.2), ("豇豆","普通大棚","第二季",0.0), ("刀豆","普通大棚","第一季",1.2), ("刀豆","普通大棚","第二季",0.0), ("芸豆","普通大棚","第一季",1.2), ("芸豆","普通大棚","第二季",0.0), ("土豆","普通大棚","第一季",0.0), ("土豆","普通大棚","第二季",0.0), ("西红柿","普通大棚","第一季",0.6), ("西红柿","普通大棚","第二季",0.0), ("茄子","普通大棚","第一季",0.6), ("茄子","普通大棚","第二季",0.0), ("菠菜","普通大棚","第一季",0.0), ("菠菜","普通大棚","第二季",0.0), ("青椒","普通大棚","第一季",0.6), ("青椒","普通大棚","第二季",0.0), ("菜花","普通大棚","第一季",0.6), ("菜花","普通大棚","第二季",0.0), ("包菜","普通大棚","第一季",0.6), ("包菜","普通大棚","第二季",0.0), ("油麦菜","普通大棚","第一季",0.9), ("油麦菜","普通大棚","第二季",0.0), ("小青菜","普通大棚","第一季",0.6), ("小青菜","普通大棚","第二季",0.0), ("黄瓜","普通大棚","第一季",0.6), ("黄瓜","普通大棚","第二季",0.0), ("生菜","普通大棚","第一季",0.3), ("生菜","普通大棚","第二季",0.0), ("辣椒","普通大棚","第一季",0.6), ("辣椒","普通大棚","第二季",0.0), ("空心菜","普通大棚","第一季",0.0), ("空心菜","普通大棚","第二季",0.0), ("黄心菜","普通大棚","第一季",0.0), ("黄心菜","普通大棚","第二季",0.0), ("芹菜","普通大棚","第一季",0.0), ("芹菜","普通大棚","第二季",0.0), ("大白菜","普通大棚","第一季",0.0), ("大白菜","普通大棚","第二季",0.0), ("白萝卜","普通大棚","第一季",0.0), ("白萝卜","普通大棚","第二季",0.0), ("红萝卜","普通大棚","第一季",0.0), ("红萝卜","普通大棚","第二季",0.0), ("榆黄菇","普通大棚","第一季",0.0), ("榆黄菇","普通大棚","第二季",1.8), ("香菇","普通大棚","第一季",0.0), ("香菇","普通大棚","第二季",1.8), ("白灵菇","普通大棚","第一季",0.0), ("白灵菇","普通大棚","第二季",1.8), ("羊肚菌","普通大棚","第一季",0.0), ("羊肚菌","普通大棚","第二季",4.2), ("黄豆","智慧大棚","第一季",0.0), ("黄豆","智慧大棚","第二季",0.0), ("黑豆","智慧大棚","第一季",0.0), ("黑豆","智慧大棚","第二季",0.0), ("红豆","智慧大棚","第一季",0.0), ("红豆","智慧大棚","第二季",0.0), ("绿豆","智慧大棚","第一季",0.0), ("绿豆","智慧大棚","第二季",0.0), ("爬豆","智慧大棚","第一季",0.0), ("爬豆","智慧大棚","第二季",0.0), ("小麦","智慧大棚","第一季",0.0), ("小麦","智慧大棚","第二季",0.0), ("玉米","智慧大棚","第一季",0.0), ("玉米","智慧大棚","第二季",0.0), ("谷子","智慧大棚","第一季",0.0), ("谷子","智慧大棚","第二季",0.0), ("高粱","智慧大棚","第一季",0.0), ("高粱","智慧大棚","第二季",0.0), ("黍子","智慧大棚","第一季",0.0), ("黍子","智慧大棚","第二季",0.0), ("荞麦","智慧大棚","第一季",0.0), ("荞麦","智慧大棚","第二季",0.0), ("南瓜","智慧大棚","第一季",0.0), ("南瓜","智慧大棚","第二季",0.0), ("红薯","智慧大棚","第一季",0.0), ("红薯","智慧大棚","第二季",0.0), ("莜麦","智慧大棚","第一季",0.0), ("莜麦","智慧大棚","第二季",0.0), ("大麦","智慧大棚","第一季",0.0), ("大麦","智慧大棚","第二季",0.0), ("水稻","智慧大棚","第一季",0.0), ("水稻","智慧大棚","第二季",0.0), ("豇豆","智慧大棚","第一季",0.6), ("豇豆","智慧大棚","第二季",0.0), ("刀豆","智慧大棚","第一季",0.0), ("刀豆","智慧大棚","第二季",0.0), ("芸豆","智慧大棚","第一季",0.6), ("芸豆","智慧大棚","第二季",0.0), ("土豆","智慧大棚","第一季",0.0), ("土豆","智慧大棚","第二季",0.0), ("西红柿","智慧大棚","第一季",0.0), ("西红柿","智慧大棚","第二季",0.3), ("茄子","智慧大棚","第一季",0.0), ("茄子","智慧大棚","第二季",0.3), ("菠菜","智慧大棚","第一季",0.0), ("菠菜","智慧大棚","第二季",0.3), ("青椒","智慧大棚","第一季",0.0), ("青椒","智慧大棚","第二季",0.3), ("菜花","智慧大棚","第一季",0.3), ("菜花","智慧大棚","第二季",0.0), ("包菜","智慧大棚","第一季",0.3), ("包菜","智慧大棚","第二季",0.0), ("油麦菜","智慧大棚","第一季",0.0), ("油麦菜","智慧大棚","第二季",0.0), ("小青菜","智慧大棚","第一季",0.0), ("小青菜","智慧大棚","第二季",0.3), ("黄瓜","智慧大棚","第一季",0.0), ("黄瓜","智慧大棚","第二季",0.3), ("生菜","智慧大棚","第一季",0.0), ("生菜","智慧大棚","第二季",0.3), ("辣椒","智慧大棚","第一季",0.0), ("辣椒","智慧大棚","第二季",0.0), ("空心菜","智慧大棚","第一季",0.3), ("空心菜","智慧大棚","第二季",0.0), ("黄心菜","智慧大棚","第一季",0.3), ("黄心菜","智慧大棚","第二季",0.0), ("芹菜","智慧大棚","第一季",0.0), ("芹菜","智慧大棚","第二季",0.3), ("大白菜","智慧大棚","第一季",0.0), ("大白菜","智慧大棚","第二季",0.0), ("白萝卜","智慧大棚","第一季",0.0), ("白萝卜","智慧大棚","第二季",0.0), ("红萝卜","智慧大棚","第一季",0.0), ("红萝卜","智慧大棚","第二季",0.0), ("榆黄菇","智慧大棚","第一季",0.0), ("榆黄菇","智慧大棚","第二季",0.0), ("香菇","智慧大棚","第一季",0.0), ("香菇","智慧大棚","第二季",0.0), ("白灵菇","智慧大棚","第一季",0.0), ("白灵菇","智慧大棚","第二季",0.0), ("羊肚菌","智慧大棚","第一季",0.0), ("羊肚菌","智慧大棚","第二季",0.0)]
# 定义预期销量矩阵 d
d = np.zeros((num_crops, num_fields, num_periods))
for crop_name, field_type, season, value in d_data:
    # 找到作物索引
    crop_index = crops.index(crop_name)
    # 找到所有匹配的地块索引
    field_indices = [idx for idx, f_type in enumerate(fields.values()) if f_type == field_type]
    # 根据季节确定时间段
    if season == '第一季':
        time_indices = [0, 2, 4, 6, 8, 10, 12, 14]
    elif season == '第二季':
        time_indices = [1, 3, 5, 7, 9, 11, 13, 15]
    else:
        continue  # 如果季节不匹配，则跳过

    # 填充预期销量矩阵
    for field_index in field_indices:
        for t in time_indices:
            d[crop_index][field_index][t] = value
d = d * 10
# 将 DataFrame 保存到 Excel 文件，每个 Period 在不同的 Sheet
excel_path = 'd.xlsx'
with pd.ExcelWriter(excel_path, engine='openpyxl') as writer:
    for t in range(num_periods):
        # 取出每个时间段的数据并创建 DataFrame
        period_data = d[:, :, t]  # 获取第 t 个时间段的数据
        df_period = pd.DataFrame(period_data)

        # 为 DataFrame 设置列名和索引名称
        field_names = [f"Field_{i+1}" for i in range(num_fields)]
        df_period.columns = field_names
        df_period.index = [f"Crop_{i+1}" for i in range(num_crops)]

        # 将 DataFrame 保存到 Excel 文件的一个新的 Sheet，以 Period 命名
        df_period.to_excel(writer, sheet_name=f'Period_{t+1}')
print(f"Data has been written to {excel_path}")
# 初始化利润矩阵 p
p = np.zeros((num_crops, num_fields, num_periods))
# 利润数据（作物名称、地块类型、种植季次、未降价净利润）
profit_data = [("黄豆","平旱地","单季",900.0), ("黑豆","平旱地","单季",3350.0), ("红豆","平旱地","单季",2950.0), ("绿豆","平旱地","单季",2100.0), ("爬豆","平旱地","单季",2451.25), ("小麦","平旱地","单季",2350.0), ("玉米","平旱地","单季",2500.0), ("谷子","平旱地","单季",2340.0), ("高粱","平旱地","单季",3380.0), ("黍子","平旱地","单季",3577.5), ("荞麦","平旱地","单季",4050.0), ("南瓜","平旱地","单季",3500.0), ("红薯","平旱地","单季",5150.0), ("莜麦","平旱地","单季",1910.0), ("大麦","平旱地","单季",1487.5), ("黄豆","梯田","单季",835.0), ("黑豆","梯田","单季",3162.5), ("红豆","梯田","单季",2785.0), ("绿豆","梯田","单季",1960.0), ("爬豆","梯田","单季",2316.25), ("小麦","梯田","单季",2210.0), ("玉米","梯田","单季",2350.0), ("谷子","梯田","单季",2205.0), ("高粱","梯田","单季",3200.0), ("黍子","梯田","单季",3390.0), ("荞麦","梯田","单季",3850.0), ("南瓜","梯田","单季",3275.0), ("红薯","梯田","单季",4825.0), ("莜麦","梯田","单季",1800.0), ("大麦","梯田","单季",1400.0), ("黄豆","山坡地","单季",770.0), ("黑豆","山坡地","单季",2975.0), ("红豆","山坡地","单季",2620.0), ("绿豆","山坡地","单季",1855.0), ("爬豆","山坡地","单季",2181.25), ("小麦","山坡地","单季",2070.0), ("玉米","山坡地","单季",2200.0), ("谷子","山坡地","单季",2070.0), ("高粱","山坡地","单季",3020.0), ("黍子","山坡地","单季",3202.5), ("荞麦","山坡地","单季",3650.0), ("南瓜","山坡地","单季",3050.0), ("红薯","山坡地","单季",4500.0), ("莜麦","山坡地","单季",1690.0), ("大麦","山坡地","单季",1312.5), ("水稻","水浇地","单季",2820.0), ("豇豆","水浇地","第一季",22000.0), ("刀豆","水浇地","第一季",12500.0), ("芸豆","水浇地","第一季",17500.0), ("土豆","水浇地","第一季",5500.0), ("西红柿","水浇地","第一季",13000.0), ("茄子","水浇地","第一季",33200.0), ("菠菜","水浇地","第一季",13225.0), ("青椒","水浇地","第一季",11000.0), ("菜花","水浇地","第一季",15750.0), ("包菜","水浇地","第一季",21150.0), ("油麦菜","水浇地","第一季",18900.0), ("小青菜","水浇地","第一季",16800.0), ("黄瓜","水浇地","第一季",81100.0), ("生菜","水浇地","第一季",19925.0), ("辣椒","水浇地","第一季",10600.0), ("空心菜","水浇地","第一季",40900.0), ("黄心菜","水浇地","第一季",20500.0), ("芹菜","水浇地","第一季",21100.0), ("豇豆","普通大棚","第一季",26400.0), ("刀豆","普通大棚","第一季",15000.0), ("芸豆","普通大棚","第一季",21000.0), ("土豆","普通大棚","第一季",6600.0), ("西红柿","普通大棚","第一季",16350.0), ("茄子","普通大棚","第一季",41600.0), ("菠菜","普通大棚","第一季",16275.0), ("青椒","普通大棚","第一季",13750.0), ("菜花","普通大棚","第一季",19000.0), ("包菜","普通大棚","第一季",25750.0), ("油麦菜","普通大棚","第一季",23000.0), ("小青菜","普通大棚","第一季",21000.0), ("黄瓜","普通大棚","第一季",101500.0), ("生菜","普通大棚","第一季",24250.0), ("辣椒","普通大棚","第一季",13300.0), ("空心菜","普通大棚","第一季",49000.0), ("黄心菜","普通大棚","第一季",24500.0), ("芹菜","普通大棚","第一季",25300.0), ("大白菜","水浇地","第二季",10500.0), ("白萝卜","水浇地","第二季",9500.0), ("红萝卜","水浇地","第二季",9250.0), ("榆黄菇","普通大棚","第二季",284500.0), ("香菇","普通大棚","第二季",74000.0), ("白灵菇","普通大棚","第二季",150000.0), ("羊肚菌","普通大棚","第二季",90000.0), ("豇豆","智慧大棚","第二季",28080.0), ("刀豆","智慧大棚","第二季",16500.0), ("芸豆","智慧大棚","第二季",22320.0), ("土豆","智慧大棚","第二季",7260.0), ("西红柿","智慧大棚","第二季",17610.0), ("茄子","智慧大棚","第二季",44880.0), ("菠菜","智慧大棚","第二季",17700.0), ("青椒","智慧大棚","第二季",16160.0), ("菜花","智慧大棚","第二季",20460.0), ("包菜","智慧大棚","第二季",28130.0), ("油麦菜","智慧大棚","第二季",24800.0), ("小青菜","智慧大棚","第二季",22640.0), ("黄瓜","智慧大棚","第二季",109550.0), ("生菜","智慧大棚","第二季",26150.0), ("辣椒","智慧大棚","第二季",14360.0), ("空心菜","智慧大棚","第二季",53900.0), ("黄心菜","智慧大棚","第二季",26410.0), ("芹菜","智慧大棚","第二季",27600.0) , ("豇豆","智慧大棚","第一季",26400.0), ("刀豆","智慧大棚","第一季",15000.0), ("芸豆","智慧大棚","第一季",21000.0), ("土豆","智慧大棚","第一季",6600.0), ("西红柿","智慧大棚","第一季",16350.0), ("茄子","智慧大棚","第一季",41600.0), ("菠菜","智慧大棚","第一季",16275.0), ("青椒","智慧大棚","第一季",13750.0), ("菜花","智慧大棚","第一季",19000.0), ("包菜","智慧大棚","第一季",25750.0), ("油麦菜","智慧大棚","第一季",23000.0), ("小青菜","智慧大棚","第一季",21000.0), ("黄瓜","智慧大棚","第一季",101500.0), ("生菜","智慧大棚","第一季",24250.0), ("辣椒","智慧大棚","第一季",13300.0), ("空心菜","智慧大棚","第一季",49000.0)]
# 填充利润矩阵 p
for crop_name, field_type, season, profit in profit_data:
    # 找到作物的索引
    crop_index = crops.index(crop_name)

    # 找到所有匹配的地块索引
    field_indices = [idx for idx, f_type in enumerate(fields.values()) if f_type == field_type]

    # 根据季节确定时间段
    if season == "单季":
        time_indices = list(range(16))  # 每年都填充
    elif season == "第一季":
        time_indices = [0, 2, 4, 6, 8, 10, 12, 14]  # 第一季
    elif season == "第二季":
        time_indices = [1, 3, 5, 7, 9, 11, 13, 15]  # 第二季
    else:
        continue  # 如果季节不匹配，则跳过

    # 填充利润矩阵
    for field_index in field_indices:
        for t in time_indices:
            p[crop_index][field_index][t] = profit
# 将 DataFrame 保存到 Excel 文件，每个 Period 在不同的 Sheet
excel_path = 'output.xlsx'
with pd.ExcelWriter(excel_path, engine='openpyxl') as writer:
    for t in range(num_periods):
        # 取出每个时间段的数据并创建 DataFrame
        period_data = p[:, :, t]  # 获取第 t 个时间段的数据
        df_period = pd.DataFrame(period_data)

        # 为 DataFrame 设置列名和索引名称
        field_names = [f"Field_{i+1}" for i in range(num_fields)]
        df_period.columns = field_names
        df_period.index = [f"Crop_{i+1}" for i in range(num_crops)]

        # 将 DataFrame 保存到 Excel 文件的一个新的 Sheet，以 Period 命名
        df_period.to_excel(writer, sheet_name=f'Period_{t+1}')
print(f"Data has been written to {excel_path}")
# s[j]: 地块 j 的面积
s = [
    80, 55, 35, 72, 68, 55, 60, 46, 40, 28,
    25, 86, 55, 44, 50, 25, 60, 45, 35, 20,
    15, 13, 15, 18, 27, 20, 15, 10, 14, 6,
    10, 12, 22, 20, 0.6, 0.6, 0.6, 0.6, 0.6,
    0.6, 0.6, 0.6, 0.6, 0.6, 0.6, 0.6, 0.6,
    0.6, 0.6, 0.6, 0.6, 0.6, 0.6, 0.6
]
s = np.array(s)
s = s * 10
# 初始化亩产量矩阵 z
z = np.zeros((num_crops, num_fields, num_periods))
# 亩产量数据（作物名称、地块类型、种植季次、亩产量/斤）
yield_data = [("黄豆","平旱地","单季",400), ("黑豆","平旱地","单季",500), ("红豆","平旱地","单季",400), ("绿豆","平旱地","单季",350), ("爬豆","平旱地","单季",415), ("小麦","平旱地","单季",800), ("玉米","平旱地","单季",1000), ("谷子","平旱地","单季",400), ("高粱","平旱地","单季",630), ("黍子","平旱地","单季",525), ("荞麦","平旱地","单季",110), ("南瓜","平旱地","单季",3000), ("红薯","平旱地","单季",2200), ("莜麦","平旱地","单季",420), ("大麦","平旱地","单季",525), ("黄豆","梯田","单季",380), ("黑豆","梯田","单季",475), ("红豆","梯田","单季",380), ("绿豆","梯田","单季",330), ("爬豆","梯田","单季",395), ("小麦","梯田","单季",760), ("玉米","梯田","单季",950), ("谷子","梯田","单季",380), ("高粱","梯田","单季",600), ("黍子","梯田","单季",500), ("荞麦","梯田","单季",105), ("南瓜","梯田","单季",2850), ("红薯","梯田","单季",2100), ("莜麦","梯田","单季",400), ("大麦","梯田","单季",500), ("黄豆","山坡地","单季",360), ("黑豆","山坡地","单季",450), ("红豆","山坡地","单季",360), ("绿豆","山坡地","单季",315), ("爬豆","山坡地","单季",375), ("小麦","山坡地","单季",720), ("玉米","山坡地","单季",900), ("谷子","山坡地","单季",360), ("高粱","山坡地","单季",570), ("黍子","山坡地","单季",475), ("荞麦","山坡地","单季",100), ("南瓜","山坡地","单季",2700), ("红薯","山坡地","单季",2000), ("莜麦","山坡地","单季",380), ("大麦","山坡地","单季",475), ("水稻","水浇地","单季",500), ("豇豆","水浇地","第一季",3000), ("刀豆","水浇地","第一季",2000), ("芸豆","水浇地","第一季",3000), ("土豆","水浇地","第一季",2000), ("西红柿","水浇地","第一季",2400), ("茄子","水浇地","第一季",6400), ("菠菜","水浇地","第一季",2700), ("青椒","水浇地","第一季",2400), ("菜花","水浇地","第一季",3300), ("包菜","水浇地","第一季",3700), ("油麦菜","水浇地","第一季",4100), ("小青菜","水浇地","第一季",3200), ("黄瓜","水浇地","第一季",12000), ("生菜","水浇地","第一季",4100), ("辣椒","水浇地","第一季",1600), ("空心菜","水浇地","第一季",10000), ("黄心菜","水浇地","第一季",5000), ("芹菜","水浇地","第一季",5500), ("豇豆","普通大棚","第一季",3600), ("刀豆","普通大棚","第一季",2400), ("芸豆","普通大棚","第一季",3600), ("土豆","普通大棚","第一季",2400), ("西红柿","普通大棚","第一季",3000), ("茄子","普通大棚","第一季",8000), ("菠菜","普通大棚","第一季",3300), ("青椒","普通大棚","第一季",3000), ("菜花","普通大棚","第一季",4000), ("包菜","普通大棚","第一季",4500), ("油麦菜","普通大棚","第一季",5000), ("小青菜","普通大棚","第一季",4000), ("黄瓜","普通大棚","第一季",15000), ("生菜","普通大棚","第一季",5000), ("辣椒","普通大棚","第一季",2000), ("空心菜","普通大棚","第一季",12000), ("黄心菜","普通大棚","第一季",6000), ("芹菜","普通大棚","第一季",6600), ("大白菜","水浇地","第二季",5000), ("白萝卜","水浇地","第二季",4000), ("红萝卜","水浇地","第二季",3000), ("榆黄菇","普通大棚","第二季",5000), ("香菇","普通大棚","第二季",4000), ("白灵菇","普通大棚","第二季",10000), ("羊肚菌","普通大棚","第二季",1000), ("豇豆","智慧大棚","第二季",3200), ("刀豆","智慧大棚","第二季",2200), ("芸豆","智慧大棚","第二季",3200), ("土豆","智慧大棚","第二季",2200), ("西红柿","智慧大棚","第二季",2700), ("茄子","智慧大棚","第二季",7200), ("菠菜","智慧大棚","第二季",3000), ("青椒","智慧大棚","第二季",2700), ("菜花","智慧大棚","第二季",3600), ("包菜","智慧大棚","第二季",4100), ("油麦菜","智慧大棚","第二季",4500), ("小青菜","智慧大棚","第二季",3600), ("黄瓜","智慧大棚","第二季",13500), ("生菜","智慧大棚","第二季",4500), ("辣椒","智慧大棚","第二季",1800), ("空心菜","智慧大棚","第二季",11000), ("黄心菜","智慧大棚","第二季",5400), ("芹菜","智慧大棚","第二季",6000)]

# 填充亩产量矩阵 z
for crop_name, field_type, season, yield_value in yield_data:
    # 找到作物的索引
    crop_index = crops.index(crop_name)

    # 找到所有匹配的地块索引
    field_indices = [idx for idx, f_type in enumerate(fields.values()) if f_type == field_type]

    # 根据季节确定时间段
    if season == "单季":
        time_indices = list(range(num_periods))  # 每年都填充
    elif season == "第一季":
        time_indices = [0, 2, 4, 6, 8, 10, 12, 14]  # 第一季
    elif season == "第二季":
        time_indices = [1, 3, 5, 7, 9, 11, 13, 15]  # 第二季
    else:
        continue  # 如果季节不匹配，则跳过

    # 填充亩产量矩阵
    for field_index in field_indices:
        for t in time_indices:
            z[crop_index][field_index][t] = yield_value
a = 100
price = [("黄豆","平旱地","单季",1300.0), ("黑豆","平旱地","单季",3750.0), ("红豆","平旱地","单季",3300.0), ("绿豆","平旱地","单季",2450.0), ("爬豆","平旱地","单季",2801.25), ("小麦","平旱地","单季",2800.0), ("玉米","平旱地","单季",3000.0), ("谷子","平旱地","单季",2700.0), ("高粱","平旱地","单季",3780.0), ("黍子","平旱地","单季",3937.5), ("荞麦","平旱地","单季",4400.0), ("南瓜","平旱地","单季",4500.0), ("红薯","平旱地","单季",7150.0), ("莜麦","平旱地","单季",2310.0), ("大麦","平旱地","单季",1837.5), ("黄豆","梯田","单季",1235.0), ("黑豆","梯田","单季",3562.5), ("红豆","梯田","单季",3135.0), ("绿豆","梯田","单季",2310.0), ("爬豆","梯田","单季",2666.25), ("小麦","梯田","单季",2660.0), ("玉米","梯田","单季",2850.0), ("谷子","梯田","单季",2565.0), ("高粱","梯田","单季",3600.0), ("黍子","梯田","单季",3750.0), ("荞麦","梯田","单季",4200.0), ("南瓜","梯田","单季",4275.0), ("红薯","梯田","单季",6825.0), ("莜麦","梯田","单季",2200.0), ("大麦","梯田","单季",1750.0), ("黄豆","山坡地","单季",1170.0), ("黑豆","山坡地","单季",3375.0), ("红豆","山坡地","单季",2970.0), ("绿豆","山坡地","单季",2205.0), ("爬豆","山坡地","单季",2531.25), ("小麦","山坡地","单季",2520.0), ("玉米","山坡地","单季",2700.0), ("谷子","山坡地","单季",2430.0), ("高粱","山坡地","单季",3420.0), ("黍子","山坡地","单季",3562.5), ("荞麦","山坡地","单季",4000.0), ("南瓜","山坡地","单季",4050.0), ("红薯","山坡地","单季",6500.0), ("莜麦","山坡地","单季",2090.0), ("大麦","山坡地","单季",1662.5), ("水稻","水浇地","单季",3500.0), ("豇豆","水浇地","第一季",24000.0), ("刀豆","水浇地","第一季",13500.0), ("芸豆","水浇地","第一季",19500.0), ("土豆","水浇地","第一季",7500.0), ("西红柿","水浇地","第一季",15000.0), ("茄子","水浇地","第一季",35200.0), ("菠菜","水浇地","第一季",15525.0), ("青椒","水浇地","第一季",12600.0), ("菜花","水浇地","第一季",18150.0), ("包菜","水浇地","第一季",24050.0), ("油麦菜","水浇地","第一季",20500.0), ("小青菜","水浇地","第一季",18400.0), ("黄瓜","水浇地","第一季",84000.0), ("生菜","水浇地","第一季",21525.0), ("辣椒","水浇地","第一季",11600.0), ("空心菜","水浇地","第一季",45000.0), ("黄心菜","水浇地","第一季",22500.0), ("芹菜","水浇地","第一季",22000.0), ("豇豆","普通大棚","第一季",28800.0), ("刀豆","普通大棚","第一季",16200.0), ("芸豆","普通大棚","第一季",23400.0), ("土豆","普通大棚","第一季",9000.0), ("西红柿","普通大棚","第一季",18750.0), ("茄子","普通大棚","第一季",44000.0), ("菠菜","普通大棚","第一季",18975.0), ("青椒","普通大棚","第一季",15750.0), ("菜花","普通大棚","第一季",22000.0), ("包菜","普通大棚","第一季",29250.0), ("油麦菜","普通大棚","第一季",25000.0), ("小青菜","普通大棚","第一季",23000.0), ("黄瓜","普通大棚","第一季",105000.0), ("生菜","普通大棚","第一季",26250.0), ("辣椒","普通大棚","第一季",14500.0), ("空心菜","普通大棚","第一季",54000.0), ("黄心菜","普通大棚","第一季",27000.0), ("芹菜","普通大棚","第一季",26400.0), ("大白菜","水浇地","第二季",12500.0), ("白萝卜","水浇地","第二季",10000.0), ("红萝卜","水浇地","第二季",9750.0), ("榆黄菇","普通大棚","第二季",287500.0), ("香菇","普通大棚","第二季",76000.0), ("白灵菇","普通大棚","第二季",160000.0), ("羊肚菌","普通大棚","第二季",100000.0), ("豇豆","智慧大棚","第二季",30720.0), ("刀豆","智慧大棚","第二季",17820.0), ("芸豆","智慧大棚","第二季",24960.0), ("土豆","智慧大棚","第二季",9900.0), ("西红柿","智慧大棚","第二季",20250.0), ("茄子","智慧大棚","第二季",47520.0), ("菠菜","智慧大棚","第二季",20700.0), ("青椒","智慧大棚","第二季",18360.0), ("菜花","智慧大棚","第二季",23760.0), ("包菜","智慧大棚","第二季",31980.0), ("油麦菜","智慧大棚","第二季",27000.0), ("小青菜","智慧大棚","第二季",24840.0), ("黄瓜","智慧大棚","第二季",113400.0), ("生菜","智慧大棚","第二季",28350.0), ("辣椒","智慧大棚","第二季",15660.0), ("空心菜","智慧大棚","第二季",59400.0), ("黄心菜","智慧大棚","第二季",29160.0), ("芹菜","智慧大棚","第二季",28800.0), ("豇豆","智慧大棚","第一季",28800.0), ("刀豆","智慧大棚","第一季",16200.0), ("芸豆","智慧大棚","第一季",23400.0), ("土豆","智慧大棚","第一季",9000.0), ("西红柿","智慧大棚","第一季",18750.0), ("茄子","智慧大棚","第一季",44000.0), ("菠菜","智慧大棚","第一季",18975.0), ("青椒","智慧大棚","第一季",15750.0), ("菜花","智慧大棚","第一季",22000.0), ("包菜","智慧大棚","第一季",29250.0), ("油麦菜","智慧大棚","第一季",25000.0), ("小青菜","智慧大棚","第一季",23000.0), ("黄瓜","智慧大棚","第一季",105000.0), ("生菜","智慧大棚","第一季",26250.0), ("辣椒","智慧大棚","第一季",14500.0), ("空心菜","智慧大棚","第一季",54000.0), ("黄心菜","智慧大棚","第一季",27000.0), ("芹菜","智慧大棚","第一季",26400.0)]
pr = np.zeros((num_crops, num_fields, num_periods))
for crop_name, field_type, season, price_value in price:
    # 找到作物的索引
    crop_index = crops.index(crop_name)
    # 找到所有匹配的地块索引
    field_indices = [idx for idx, f_type in enumerate(fields.values()) if f_type == field_type]
    # 根据季节确定时间段
    if season == "单季":
        time_indices = list(range(num_periods))  # 每年都填充
    elif season == "第一季":
        time_indices = [0, 2, 4, 6, 8, 10, 12, 14]  # 第一季
    elif season == "第二季":
        time_indices = [1, 3, 5, 7, 9, 11, 13, 15]  # 第二季
    else:
        continue  # 如果季节不匹配，则跳过

    for field_index in field_indices:
        for t in time_indices:
            pr[crop_index][field_index][t] = price_value
# 将 DataFrame 保存到 Excel 文件，每个 Period 在不同的 Sheet
excel_path = 'price.xlsx'
with pd.ExcelWriter(excel_path, engine='openpyxl') as writer:
    for t in range(num_periods):
        # 取出每个时间段的数据并创建 DataFrame
        period_data = pr[:, :, t]  # 获取第 t 个时间段的数据
        df_period = pd.DataFrame(period_data)

        # 为 DataFrame 设置列名和索引名称
        field_names = [f"Field_{i+1}" for i in range(num_fields)]
        df_period.columns = field_names
        df_period.index = [f"Crop_{i+1}" for i in range(num_crops)]

        # 将 DataFrame 保存到 Excel 文件的一个新的 Sheet，以 Period 命名
        df_period.to_excel(writer, sheet_name=f'Period_{t+1}')
cost = [("黄豆","平旱地","单季",400), ("黑豆","平旱地","单季",400), ("红豆","平旱地","单季",350), ("绿豆","平旱地","单季",350), ("爬豆","平旱地","单季",350), ("小麦","平旱地","单季",450), ("玉米","平旱地","单季",500), ("谷子","平旱地","单季",360), ("高粱","平旱地","单季",400), ("黍子","平旱地","单季",360), ("荞麦","平旱地","单季",350), ("南瓜","平旱地","单季",1000), ("红薯","平旱地","单季",2000), ("莜麦","平旱地","单季",400), ("大麦","平旱地","单季",350), ("黄豆","梯田","单季",400), ("黑豆","梯田","单季",400), ("红豆","梯田","单季",350), ("绿豆","梯田","单季",350), ("爬豆","梯田","单季",350), ("小麦","梯田","单季",450), ("玉米","梯田","单季",500), ("谷子","梯田","单季",360), ("高粱","梯田","单季",400), ("黍子","梯田","单季",360), ("荞麦","梯田","单季",350), ("南瓜","梯田","单季",1000), ("红薯","梯田","单季",2000), ("莜麦","梯田","单季",400), ("大麦","梯田","单季",350), ("黄豆","山坡地","单季",400), ("黑豆","山坡地","单季",400), ("红豆","山坡地","单季",350), ("绿豆","山坡地","单季",350), ("爬豆","山坡地","单季",350), ("小麦","山坡地","单季",450), ("玉米","山坡地","单季",500), ("谷子","山坡地","单季",360), ("高粱","山坡地","单季",400), ("黍子","山坡地","单季",360), ("荞麦","山坡地","单季",350), ("南瓜","山坡地","单季",1000), ("红薯","山坡地","单季",2000), ("莜麦","山坡地","单季",400), ("大麦","山坡地","单季",350), ("水稻","水浇地","单季",680), ("豇豆","水浇地","第一季",2000), ("刀豆","水浇地","第一季",1000), ("芸豆","水浇地","第一季",2000), ("土豆","水浇地","第一季",2000), ("西红柿","水浇地","第一季",2000), ("茄子","水浇地","第一季",2000), ("菠菜","水浇地","第一季",2300), ("青椒","水浇地","第一季",1600), ("菜花","水浇地","第一季",2400), ("包菜","水浇地","第一季",2900), ("油麦菜","水浇地","第一季",1600), ("小青菜","水浇地","第一季",1600), ("黄瓜","水浇地","第一季",2900), ("生菜","水浇地","第一季",1600), ("辣椒","水浇地","第一季",1000), ("空心菜","水浇地","第一季",4100), ("黄心菜","水浇地","第一季",2000), ("芹菜","水浇地","第一季",900), ("豇豆","普通大棚","第一季",2400), ("刀豆","普通大棚","第一季",1200), ("芸豆","普通大棚","第一季",2400), ("土豆","普通大棚","第一季",2400), ("西红柿","普通大棚","第一季",2400), ("茄子","普通大棚","第一季",2400), ("菠菜","普通大棚","第一季",2700), ("青椒","普通大棚","第一季",2000), ("菜花","普通大棚","第一季",3000), ("包菜","普通大棚","第一季",3500), ("油麦菜","普通大棚","第一季",2000), ("小青菜","普通大棚","第一季",2000), ("黄瓜","普通大棚","第一季",3500), ("生菜","普通大棚","第一季",2000), ("辣椒","普通大棚","第一季",1200), ("空心菜","普通大棚","第一季",5000), ("黄心菜","普通大棚","第一季",2500), ("芹菜","普通大棚","第一季",1100), ("大白菜","水浇地","第二季",2000), ("白萝卜","水浇地","第二季",500), ("红萝卜","水浇地","第二季",500), ("榆黄菇","普通大棚","第二季",3000), ("香菇","普通大棚","第二季",2000), ("白灵菇","普通大棚","第二季",10000), ("羊肚菌","普通大棚","第二季",10000), ("豇豆","智慧大棚","第二季",2640), ("刀豆","智慧大棚","第二季",1320), ("芸豆","智慧大棚","第二季",2640), ("土豆","智慧大棚","第二季",2640), ("西红柿","智慧大棚","第二季",2640), ("茄子","智慧大棚","第二季",2640), ("菠菜","智慧大棚","第二季",3000), ("青椒","智慧大棚","第二季",2200), ("菜花","智慧大棚","第二季",3300), ("包菜","智慧大棚","第二季",3850), ("油麦菜","智慧大棚","第二季",2200), ("小青菜","智慧大棚","第二季",2200), ("黄瓜","智慧大棚","第二季",3850), ("生菜","智慧大棚","第二季",2200), ("辣椒","智慧大棚","第二季",1300), ("空心菜","智慧大棚","第二季",5500), ("黄心菜","智慧大棚","第二季",2750), ("芹菜","智慧大棚","第二季",1200), ("豇豆","智慧大棚","第一季",2400), ("刀豆","智慧大棚","第一季",1200), ("芸豆","智慧大棚","第一季",2400), ("土豆","智慧大棚","第一季",2400), ("西红柿","智慧大棚","第一季",2400), ("茄子","智慧大棚","第一季",2400), ("菠菜","智慧大棚","第一季",2700), ("青椒","智慧大棚","第一季",2000), ("菜花","智慧大棚","第一季",3000), ("包菜","智慧大棚","第一季",3500), ("油麦菜","智慧大棚","第一季",2000), ("小青菜","智慧大棚","第一季",2000), ("黄瓜","智慧大棚","第一季",3500), ("生菜","智慧大棚","第一季",2000), ("辣椒","智慧大棚","第一季",1200), ("空心菜","智慧大棚","第一季",5000), ("黄心菜","智慧大棚","第一季",2500), ("芹菜","智慧大棚","第一季",1100)]
co = np.zeros((num_crops, num_fields, num_periods))
for crop_name, field_type, season, cost_value in cost:
    # 找到作物的索引
    crop_index = crops.index(crop_name)
    # 找到所有匹配的地块索引
    field_indices = [idx for idx, f_type in enumerate(fields.values()) if f_type == field_type]
    # 根据季节确定时间段
    if season == "单季":
        time_indices = list(range(num_periods))  # 每年都填充
    elif season == "第一季":
        time_indices = [0, 2, 4, 6, 8, 10, 12, 14]  # 第一季
    elif season == "第二季":
        time_indices = [1, 3, 5, 7, 9, 11, 13, 15]  # 第二季
    else:
        continue  # 如果季节不匹配，则跳过

    for field_index in field_indices:
        for t in time_indices:
            co[crop_index][field_index][t] = cost_value
# 将 DataFrame 保存到 Excel 文件，每个 Period 在不同的 Sheet
excel_path = 'cost.xlsx'
with pd.ExcelWriter(excel_path, engine='openpyxl') as writer:
    for t in range(num_periods):
        # 取出每个时间段的数据并创建 DataFrame
        period_data = co[:, :, t]  # 获取第 t 个时间段的数据
        df_period = pd.DataFrame(period_data)

        # 为 DataFrame 设置列名和索引名称
        field_names = [f"Field_{i+1}" for i in range(num_fields)]
        df_period.columns = field_names
        df_period.index = [f"Crop_{i+1}" for i in range(num_crops)]

        # 将 DataFrame 保存到 Excel 文件的一个新的 Sheet，以 Period 命名
        df_period.to_excel(writer, sheet_name=f'Period_{t+1}')
# 目标函数
objective_expr = pulp.lpSum((pr[i][j][t] * c[i][j][t] - co[i][j][t] * x[i][j][t]) for i in range(41) for j in range(54) for t in range(16))
cf = pulp.lpSum(a * y[i][j][t] for i in range(0, 41) for j in range(0, 54) for t in range(0, 16))
objective_1 = objective_expr - cf
prob += objective_1
# 约束条件
# 1. 避免重茬种植
for t in range(0, 15):
    for i in range(0, 41):
        for j in range(49, 54):
            prob += y[i][j][t] + y[i][j][t + 1] <= 1
for t in range(0, 14, 2):
    for j in range(26, 34):
        prob += y[15][j][t] + y[15][j][t + 2] <= 1
    for i in range(0, 41):
        for j in range(0, 26):
            prob += y[i][j][t] + y[i][j][t + 2] <= 1

# 2. 地块种植作物的约束
for t in range(0, 16):
    for i in range(15, 41):
        for j in range(0, 26):
            prob += y[i][j][t] == 0

for t in range(0, 16, 2):
    for i in range(0, 15):
        for j in range(26, 34):
            prob += y[i][j][t] == 0
    for i in range(34, 41):
        for j in range(26, 34):
            prob += y[i][j][t] == 0

for t in range(1, 16, 2):
    for i in range(0, 34):
        for j in range(26, 34):
            prob += y[i][j][t] == 0
    for i in range(37, 41):
        for j in range(26, 34):
            prob += y[i][j][t] == 0

for t in range(0, 16, 2):
    for i in range(0, 16):
        for j in range(34, 50):
            prob += y[i][j][t] == 0
    for i in range(34, 41):
        for j in range(34, 50):
            prob += y[i][j][t] == 0

for t in range(1, 16, 2):
    for i in range(0, 37):
        for j in range(34, 50):
            prob += y[i][j][t] == 0

for t in range(0, 16):
    for i in range(0, 16):
        for j in range(50, 54):
            prob += y[i][j][t] == 0
    for i in range(34, 41):
        for j in range(50, 54):
            prob += y[i][j][t] == 0

# 3. 豆类作物种植约束
# 每个地块的所有土地三年内至少种植一次豆类作物的约束
for j in range(0, 54):
    for m in range(0, 11):
        prob += pulp.lpSum(y[i][j][t] for t in range(m, m + 6) for i in [0, 1, 2, 3, 4, 16, 17, 18]) >= 1
for i in range(0, 5):
    for j in range(0, 54):
        prob += x[i][j][t] == s[j] * y[i][j][t]
for i in range(16, 19):
    for j in range(0, 54):
        prob += x[i][j][t] == s[j] * y[i][j][t]
# 4. 单个地块种植面积限制
for t in range(0, 16):
    for i in range(0, 41):
        for j in range(0, 54):
            prob += (1/4) * s[j] * y[i][j][t] <= x[i][j][t]
            prob += x[i][j][t] <= s[j] * y[i][j][t]

for t in range(0, 16):
    for j in range(0, 54):
        prob += pulp.lpSum(x[i][j][t] for i in range(0, 41)) <= s[j]

# 5. 产量与0-1变量之间的关系
M = 1000  # 足够大的正数
# 产量x与0-1变量y的关系
for t in range(0, 16):
    for i in range(0, 41):
        for j in range(0, 54):
            prob += y[i][j][t] <= x[i][j][t]
            prob += x[i][j][t] <= y[i][j][t] * M

# 6. 销量与产量关系
for i in range(41):
    for j in range(54):
        for t in range(16):
            prob += c[i][j][t] <= x[i][j][t]

for i in range(41):
    for t in range(16):
        prob += 6 * pulp.lpSum(c[i][j][t] for j in range(0, 6)) <= pulp.lpSum(d[i][j][t] for j in range(0, 6))
for i in range(41):
    for t in range(16):
        prob += 14 * pulp.lpSum(c[i][j][t] for j in range(6, 20)) <= pulp.lpSum(d[i][j][t] for j in range(6, 20))
for i in range(41):
    for t in range(16):
        prob += 6 * pulp.lpSum(c[i][j][t] for j in range(20, 26)) <= pulp.lpSum(d[i][j][t] for j in range(20, 26))
for i in range(41):
    for t in range(16):
        prob += 8 * pulp.lpSum(c[i][j][t] for j in range(26, 34)) <= pulp.lpSum(d[i][j][t] for j in range(26, 34))
for i in range(41):
    for t in range(16):
        prob += 16 * pulp.lpSum(c[i][j][t] for j in range(34, 50)) <= pulp.lpSum(d[i][j][t] for j in range(34, 50))
for i in range(41):
    for t in range(16):
        prob += 4 * pulp.lpSum(c[i][j][t] for j in range(50, 54)) <= pulp.lpSum(d[i][j][t] for j in range(50, 54))

for i in range(41):
    for t in range(16):
        prob += 6 * pulp.lpSum(c[i][j][t] for j in range(0, 6)) >= 0.7*pulp.lpSum(d[i][j][t] for j in range(0, 6))
for i in range(41):
    for t in range(16):
        prob += 14 * pulp.lpSum(c[i][j][t] for j in range(6, 20)) >= 0.7*pulp.lpSum(d[i][j][t] for j in range(6, 20))
for i in range(41):
    for t in range(16):
        prob += 6 * pulp.lpSum(c[i][j][t] for j in range(20, 26)) >= 0.7*pulp.lpSum(d[i][j][t] for j in range(20, 26))
for i in range(41):
    for t in range(16):
        prob += 8 * pulp.lpSum(c[i][j][t] for j in range(26, 34)) >= 0.7*pulp.lpSum(d[i][j][t] for j in range(26, 34))
for i in range(41):
    for t in range(16):
        prob += 16 * pulp.lpSum(c[i][j][t] for j in range(34, 50)) >= 0.7*pulp.lpSum(d[i][j][t] for j in range(34, 50))
for i in range(41):
    for t in range(16):
        prob += 4 * pulp.lpSum(x[i][j][t] for j in range(50, 54)) == pulp.lpSum(d[i][j][t] for j in range(50, 54))

# 7. 地块单季约束
for t in range(1, 17, 2):
    for i in range(15, 16):
        for j in range(26, 35):
            prob += y[i][j][t] == 0
for t in range(1, 17, 2):
    for i in range(0, 41):
        for j in range(0, 26):
            prob += y[i][j][t] == 0
for t in range(0, 16, 2):
    for j in range(26,34):
        for i in range(0,41):
            y[i][j][t+1] <= 1 - y[15][j][t]
for i in range(41):
    for j in range(54):
        for t in range(2):
            prob += x[i][j][t] == 10 * crops_array[i][j][t]
# 设置 Cbc 求解器的参数
pulp_CBC = pulp.PULP_CBC_CMD(msg=1, timeLimit=30, maxNodes=1000)

# 求解模型
prob.solve(pulp_CBC)

# 输出目标函数，并缩小十倍
objective_function_value = pulp.value(objective_expr) / 10
print(f"Objective Function = {objective_function_value}")

# 提取决策变量 x 的值，并缩小十倍
x_values = {(i, j, t): pulp.value(x[i][j][t]) / 10 for i in range(41) for j in range(54) for t in range(16)}

# 准备索引和列名
index_names = [f'Crop_{i+1}' for i in range(41)]
columns_names = [f'Field_{j+1}' for j in range(54)]

# 将 DataFrame 保存到 Excel 文件，每个 Period 在不同的 Sheet
excel_path = 'x_values.xlsx'
with pd.ExcelWriter(excel_path, engine='openpyxl') as writer:
    for t in range(16):
        # 取出每个时间段的数据并创建 DataFrame，并缩小十倍
        data = [[x_values.get((i, j, t), 0) for j in range(54)] for i in range(41)]
        df_period = pd.DataFrame(data, index=index_names, columns=columns_names)

        # 将 DataFrame 保存到 Excel 文件的一个新的 Sheet，以 Period 命名
        df_period.to_excel(writer, sheet_name=f'Period_{t+1}')

print(f"X values have been written to {excel_path}")

# 提取决策变量 y 的值，并缩小十倍
y_values = {(i, j, t): pulp.value(y[i][j][t]) / 10 for i in range(41) for j in range(54) for t in range(16)}

# 准备索引和列名
index_names = [f'Crop_{i+1}' for i in range(41)]
columns_names = [f'Field_{j+1}' for j in range(54)]

# 将 DataFrame 保存到 Excel 文件，每个 Period 在不同的 Sheet
excel_path = 'y_values.xlsx'
with pd.ExcelWriter(excel_path, engine='openpyxl') as writer:
    for t in range(16):
        # 取出每个时间段的数据并创建 DataFrame，并缩小十倍
        data = [[y_values.get((i, j, t), 0) for j in range(54)] for i in range(41)]
        df_period = pd.DataFrame(data, index=index_names, columns=columns_names)

        # 将 DataFrame 保存到 Excel 文件的一个新的 Sheet，以 Period 命名
        df_period.to_excel(writer, sheet_name=f'Period_{t+1}')

print(f"Y values have been written to {excel_path}")